﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class BOMManager:ConnectionManager
    {
        public SqlException AddBOM(BOMUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT INTO OBOM (BOMCode, BOMName, ItemCode, ItemName, Qty, UoM, IsActive) VALUES (@BOMCode, @BOMName, @ItemCode, @ItemName, @Qty, @UoM, @IsActive)";

                oCommand.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));
                oCommand.Parameters.Add(new SqlParameter("@BOMName", oUnit.BOMDescription));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oUnit.ProductCode));
                oCommand.Parameters.Add(new SqlParameter("@ItemName", oUnit.ProductDscription));
                oCommand.Parameters.Add(new SqlParameter("@Qty", oUnit.Qty));
                oCommand.Parameters.Add(new SqlParameter("@UoM", oUnit.UoM));

                oCommand.Parameters.Add(new SqlParameter("@IsActive", oUnit.IsBOMActive));

                oCommand.ExecuteNonQuery();

                foreach (DataRow oRow in oUnit.BOMLineItems.Rows)
                {
                    SqlCommand oLineCommand = new SqlCommand();

                    oLineCommand.Connection = this.Connection;

                    oLineCommand.CommandText = "INSERT INTO BOM1 (BOMCode, ItemGroup, ItemCode, ItemName, Qty, UoM, WhseCode) VALUES (@BOMCode, @ItemGroup, @ItemCode, @ItemName, @Qty, @UoM, @WhseCode)";
                    oLineCommand.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));
                    oLineCommand.Parameters.Add(new SqlParameter("@ItemGroup", oRow["ItemGroup"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@Qty", oRow["Qty"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@WhseCode", oRow["WhseCode"].ToString()));

                    oLineCommand.ExecuteNonQuery();
                }
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateBOM(BOMUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE OBOM SET IsActive=@IsActive, BOMName=@BOMName, ItemCode=@ItemCode, ItemName=@ItemName, Qty=@Qty, UoM=@UoM WHERE BOMCode=@BOMCode";

                oCommand.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));
                oCommand.Parameters.Add(new SqlParameter("@BOMName", oUnit.BOMDescription));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oUnit.ProductCode));
                oCommand.Parameters.Add(new SqlParameter("@ItemName", oUnit.ProductDscription));
                oCommand.Parameters.Add(new SqlParameter("@Qty", oUnit.Qty));
                oCommand.Parameters.Add(new SqlParameter("@UoM", oUnit.UoM));
                oCommand.Parameters.Add(new SqlParameter("@IsActive", oUnit.IsBOMActive));

                oCommand.ExecuteNonQuery();

                SqlCommand oRemoveLineItems = new SqlCommand();

                oRemoveLineItems.Connection = this.Connection;
                oRemoveLineItems.CommandText = "DELETE BOM1 WHERE BOMCode=@BOMCode";
                oRemoveLineItems.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));
                oRemoveLineItems.ExecuteNonQuery();

                foreach (DataRow oRow in oUnit.BOMLineItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        SqlCommand oLineCommand = new SqlCommand();

                        oLineCommand.Connection = this.Connection;

                        oLineCommand.CommandText = "INSERT INTO BOM1 (BOMCode, ItemGroup, ItemCode, ItemName, Qty, UoM, WhseCode) VALUES (@BOMCode, @ItemGroup, @ItemCode, @ItemName, @Qty, @UoM, @WhseCode)";
                        oLineCommand.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));
                        oLineCommand.Parameters.Add(new SqlParameter("@ItemGroup", oRow["ItemGroup"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@Qty", oRow["Qty"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@WhseCode", oRow["WhseCode"].ToString()));

                        oLineCommand.ExecuteNonQuery();
                    }
                }

                oUnit.BOMLineItems.AcceptChanges();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public bool IsBOMExists(string BOMCode)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();


            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBOM WHERE BOMCode=@BOMCode";
            oCommand.Parameters.Add(new SqlParameter("@BOMCode", BOMCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            int iRowCount = dt.Rows.Count;

            if (iRowCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public DataTable GetProductStructures()
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();


            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBOM";

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetProductStructure(string _BOM_CODE)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();


            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBOM WHERE BOMCode=@BOMCode";
            oCommand.Parameters.Add(new SqlParameter("@BOMCode", _BOM_CODE));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetProductStructureLineItems(string _BOM_CODE)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();


            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM BOM1 WHERE BOMCode=@BOMCode";
            oCommand.Parameters.Add(new SqlParameter("@BOMCode", _BOM_CODE));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseStructureByName(string sBOMName)
        {
            DataSet ds = new DataSet();
            sBOMName = '%' + sBOMName + '%';
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBOM WHERE BOMName LIKE @BOMName";
            oCommand.Parameters.Add(new SqlParameter("@BOMName", sBOMName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable BrowseStructureByCode(string sBOMCode)
        {
            DataSet ds = new DataSet();
            sBOMCode = '%' + sBOMCode + '%';
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBOM WHERE BOMCode LIKE @BOMCode";
            oCommand.Parameters.Add(new SqlParameter("@BOMCode", sBOMCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }


        public DataTable GetStructureByProductCode(string sProductCode)
        {
            DataSet ds = new DataSet();
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBOM WHERE ItemCode = @ItemCode";
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", sProductCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetMaterials(string sBOMCode)
        {
            DataSet ds = new DataSet();
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_BOM_WITH_STOCKS WHERE BOMCode = @BOMCode";
            oCommand.Parameters.Add(new SqlParameter("@BOMCode", sBOMCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

    }
}

